/* autor Konjkov@gmail.ru */

#include <stdio.h>
#include <string.h>

#include "system.h"
#include "common.h"

int system_install() {
SQLHSTMT   stmt;
SQLRETURN  retcode;

    system_uninstall();

// create schema SQL_SCHEMA;
// create language "plpgsql";

#define SQL_SYSTEM_TABLE1 "CREATE TABLE "SQL_SCHEMA".syncMAIN ( \
version_major smallint not null, \
version_minor smallint not null, \
current_packet int default 0 not null)"

#define SQL_SYSTEM_TABLE2 "CREATE TABLE "SQL_SCHEMA".syncNODES ( \
name varchar(30) not null unique, \
prepared int default 0 not null, \
updated int default 0 not null)"

#define SQL_SYSTEM_TABLE3 "CREATE TABLE "SQL_SCHEMA".syncSTREAM ( \
sender varchar(30) not null, \
receiver varchar(30) not null, \
stage int not null, \
num_packet int not null, \
time_packet timestamp default current_timestamp not null, \
primary key (sender, receiver, stage, num_packet) )"

#define SQL_SYSTEM_TABLE4 "CREATE TABLE "SQL_SCHEMA".syncPROCESSING ( \
sender varchar(30) not null, \
receiver varchar(30) not null, \
num_packet int not null, \
dt_receive timestamp, \
dt_process timestamp, \
st_process int default 0, \
primary key (sender, receiver, num_packet))"

#define SQL_FUNCTION_SYNC_STREAM "CREATE OR REPLACE FUNCTION "SQL_SCHEMA".syncNODES_Stream_Func_AU() \
RETURNS trigger AS \
\$BODY\$ \
BEGIN \
if "SQL_FIELDPREFIX_OLD".prepared < "SQL_FIELDPREFIX_NEW".prepared \
then \
    INSERT INTO "SQL_SCHEMA".syncSTREAM(sender, receiver, stage, num_packet, time_packet) \
    VALUES("THIS", "SQL_FIELDPREFIX_NEW".name, 0, "SQL_FIELDPREFIX_NEW".prepared, now()); \
end if; \
if "SQL_FIELDPREFIX_OLD".updated < "SQL_FIELDPREFIX_NEW".updated \
then \
    INSERT INTO "SQL_SCHEMA".syncSTREAM(sender, receiver, stage, num_packet, time_packet) \
    VALUES("SQL_FIELDPREFIX_NEW".name, "THIS", 0, "SQL_FIELDPREFIX_NEW".updated, now()); \
end if; \
RETURN NULL; \
END; \
\$BODY\$ \
LANGUAGE 'plpgsql'"

#define SQL_TRIGGER_SYNC_STREAM "CREATE TRIGGER syncNODES_Stream_AU \
AFTER UPDATE on "SQL_SCHEMA".syncNODES \
FOR EACH ROW EXECUTE PROCEDURE "SQL_SCHEMA".syncNODES_Stream_Func_AU()"

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_SYSTEM_TABLE1, SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_SYSTEM_TABLE2, SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_SYSTEM_TABLE3, SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_SYSTEM_TABLE4, SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_FUNCTION_SYNC_STREAM, SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_TRIGGER_SYNC_STREAM, SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, "INSERT INTO mcc.syncMAIN(version_major, version_minor) VALUES("DATA_VER_MAJOR","DATA_VER_MINOR")", SQL_NTS ) );
    SQLFreeStmt( stmt, SQL_CLOSE );
    return 0;
}

int system_uninstall() {
SQLHSTMT   stmt;
SQLRETURN  retcode;

    SQLAllocStmt( dbc, &stmt );
    retcode = SQLExecDirect( stmt, "DROP TABLE mcc.syncMAIN", SQL_NTS );
    if ( retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ) {
        SQLFreeStmt( stmt, SQL_CLOSE );
    } else {
        syncctl_error( stmt , __FILE__, __LINE__ );
        SQLFreeStmt( stmt, SQL_DROP );
    }

    SQLAllocStmt( dbc, &stmt );
    if ( !SQL_SUCCEEDED( SQLExecDirect( stmt, "DROP TABLE mcc.syncNODES", SQL_NTS ) ) ) {
        syncctl_error( stmt );
    } else {
        SQLFreeStmt( stmt, SQL_CLOSE );
    }

    SQLAllocStmt( dbc, &stmt );
    if ( !SQL_SUCCEEDED( SQLExecDirect( stmt, "DROP TABLE mcc.syncSTREAM", SQL_NTS ) ) ) {
        syncctl_error( stmt );
    } else {
        SQLFreeStmt( stmt, SQL_CLOSE );
    }

    SQLAllocStmt( dbc, &stmt );
    if ( !SQL_SUCCEEDED( SQLExecDirect( stmt, "DROP TABLE mcc.syncPROCESSING", SQL_NTS ) ) ) {
        syncctl_error( stmt );
    } else {
        SQLFreeStmt( stmt, SQL_CLOSE );
    }

    SQLAllocStmt( dbc, &stmt );
    if ( !SQL_SUCCEEDED( SQLExecDirect( stmt, "DROP TRIGGER mcc.syncNODES_Stream_AU()", SQL_NTS ) ) ) {
        syncctl_error( stmt );
    } else {
        SQLFreeStmt( stmt, SQL_CLOSE );
    }

    SQLAllocStmt( dbc, &stmt );
    if ( !SQL_SUCCEEDED( SQLExecDirect( stmt, "DROP FUNCTION mcc.syncNODES_Stream_Func_AU()", SQL_NTS ) ) ) {
        syncctl_error( stmt );
    } else {
        SQLFreeStmt( stmt, SQL_CLOSE );
    }

    return 0;
}

int system_update() {
SQLHSTMT   stmt;

    return 0;
}

int system_status() {
SQLHSTMT   stmt;
SQLRETURN  retcode;

// Вернуть версию метаданных системы в БД и текущий номер пакета
#define SQL_SYSTEM_STATUS_VERSION "SELECT version_major, version_minor, current_packet FROM "SQL_SCHEMA".syncMAIN"

// Вернуть список удаленных узлов, зарегистрированных в системе
#define SQL_SYSTEM_STATUS_NODES "SELECT name, prepared, updated FROM "SQL_SCHEMA".syncNODES ORDER BY name"

// Вернуть список синхронизируемых таблиц
#define SQL_TABLELIST "SELECT DISTINCT table_schema || '.' || table_name \
FROM information_schema.columns \
WHERE column_name = '"SQL_SYSFIELD1"' \
AND table_name NOT LIKE '%_"SQL_PREFIX_DELTABLE"' \
AND table_name NOT LIKE '%_"SQL_PREFIX_TEMPTABLE"' \
AND table_name NOT LIKE '%_"SQL_PREFIX_UNLOADTABLE"'"


// Вернуть признак установлена ли система в базе данных.
char table_catalog[20];
char table_schema[20];
char table_name[20];
char table_type[20];
char table_remarks[20];

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLTables( stmt, NULL, 0, SQL_SCHEMA, SQL_NTS, "syncmain", SQL_NTS, "TABLE", SQL_NTS) );
    EXEC_SQL( SQLBindCol( stmt, 1, SQL_C_CHAR, table_catalog, 20, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 2, SQL_C_CHAR, table_schema, 20, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 3, SQL_C_CHAR, table_name, 20, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 4, SQL_C_CHAR, table_type, 20, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 5, SQL_C_CHAR, table_remarks, 20, SQL_TYPE_NULL ) );

    retcode = SQLFetch( stmt );
    if ( retcode == SQL_ERROR ) {
        return syncctl_error( stmt );
    } else if ( retcode == SQL_NO_DATA ) {
        printf("Система не установлена в базе данных\n");
        SQLFreeStmt( stmt, SQL_CLOSE );
        return 0;
    } else {
        printf("%s %s %s %s %s\n",table_catalog, table_schema, table_name, table_type, table_remarks);
    }
    SQLFreeStmt( stmt, SQL_CLOSE );


// Вернуть версию метаданных системы в БД и текущий номер пакета
// todo smallint
char version_major[30];
char version_minor[30];
int current_packet;

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_SYSTEM_STATUS_VERSION, SQL_NTS ) );
    EXEC_SQL( SQLBindCol( stmt, 1, SQL_C_CHAR, version_major, 30, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 2, SQL_C_CHAR, version_minor, 30, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 3, SQL_C_LONG, &current_packet, 0, SQL_TYPE_NULL ) );

    retcode = SQLFetch( stmt );
    if ( retcode == SQL_ERROR ) {
        return syncctl_error( stmt );
    } else if ( retcode == SQL_NO_DATA ) {
        printf("Информация о версии системы отсутствует\n");
        printf("Текущий номер пакета не указан\n");
        return 1;
    } else {
        printf("Версия метаданных системы в БД: %s.%s\n", version_major, version_minor);
        printf("Текущий пакет: %i\n", current_packet);
    }
    SQLFreeStmt( stmt, SQL_CLOSE );

// Вернуть список удаленных узлов, зарегистрированных в системе
char name[MAX_NAME_LEN];
int prepared;
int updated;

    printf("Удаленные узлы:\n");

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_SYSTEM_STATUS_NODES, SQL_NTS ) );
    EXEC_SQL( SQLBindCol( stmt, 1, SQL_C_CHAR, name, MAX_NAME_LEN, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 2, SQL_C_LONG, &prepared, 0, SQL_TYPE_NULL ) );
    EXEC_SQL( SQLBindCol( stmt, 3, SQL_C_LONG, &updated, 0, SQL_TYPE_NULL ) );
//    while (1) {
//        retcode = SQLFetch( stmt );
//        if ( retcode == SQL_ERROR ) {
//            return syncctl_error( stmt );
//        } else if ( retcode == SQL_NO_DATA ) {
//            break;
//        } else {
//            printf("\t%s:[%i,%i]\n", name, prepared, updated);
//        }
//    }

    while ( (retcode = SQLFetch( stmt )) != SQL_NO_DATA ) {
        if ( retcode == SQL_ERROR ) {
            return syncctl_error( stmt, __FILE__, __LINE__ );
        }
        printf("\t%s:[%i,%i]\n", name, prepared, updated);
    }
    SQLFreeStmt( stmt, SQL_CLOSE );

// Вернуть список синхронизируемых таблиц

char table[MAX_NAME_LEN];

    printf("Синхронизируемые таблицы:\n");

    SQLAllocStmt( dbc, &stmt );
    EXEC_SQL( SQLExecDirect( stmt, SQL_TABLELIST, SQL_NTS ) );
    EXEC_SQL( SQLBindCol( stmt, 1, SQL_C_CHAR, table, MAX_NAME_LEN, SQL_TYPE_NULL ) );

    while (1) {
        retcode = SQLFetch( stmt );
        if ( retcode == SQL_ERROR ) {
            return syncctl_error( stmt, __FILE__, __LINE__  );
        } else if ( retcode == SQL_NO_DATA ) {
            break;
        } else {
            printf("\t%s\n", table);
        }
    }
    SQLFreeStmt( stmt, SQL_CLOSE );

    return 0;
}


